package com.ndood.spider.tmall.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import com.ndood.spider.tmall.bean.TmallGoods;

@Component
public class TmallGoodsDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	private RowMapper<TmallGoods> rowMapper = new RowMapper<TmallGoods>() {
		@Override
		public TmallGoods mapRow(ResultSet rs, int rowNum) throws SQLException {
			TmallGoods g = new TmallGoods();
			Gson gson = new Gson();
			g.setBrand(rs.getString("brand"));
			g.setCat(rs.getString("cat"));
			g.setCatPath(rs.getString("catPath"));
			g.setCommentsCount(rs.getString("commentsCount"));
			g.setId(rs.getString("id"));
			g.setName(rs.getString("name"));
			g.setPrice(rs.getString("price"));
			g.setSalesCount(rs.getString("salesCount"));
			g.setShopName(rs.getString("shopName"));
			g.setUrl(rs.getString("url"));
			g.setDescUrls(gson.fromJson(rs.getString("descUrls"), new TypeToken<HashMap<String, String>>() {
			}.getType()));
			g.setColorUrls(gson.fromJson(rs.getString("colorUrls"), new TypeToken<HashMap<String, String>>() {
			}.getType()));
			g.setImgUrls(gson.fromJson(rs.getString("imgUrls"), new TypeToken<HashMap<String, String>>() {
			}.getType()));
			g.setParams(gson.fromJson(rs.getString("params"), new TypeToken<HashMap<String, String>>() {
			}.getType()));
			return g;
		}
	};

	/**
	 * 查询商品列表
	 * 
	 * @return
	 */
	public List<TmallGoods> getList() {
		String sql = "select * from tmall_goods";
		return (List<TmallGoods>) jdbcTemplate.query(sql, rowMapper);
	}

	/**
	 * 查询一条数据
	 * 
	 * @param id
	 * @return
	 */
	public TmallGoods get(String id) {
		String sql = "select * from tmall_goods where id = ?";
		Object[] params = new Object[] { id };
		List<TmallGoods> list = (List<TmallGoods>) jdbcTemplate.query(sql, params, rowMapper);
		if(list==null||list.size()==0){
			return null;
		}
		return list.get(0);
	}

	/**
	 * 插入一条数据
	 * 
	 * @param goods
	 * @return
	 */
	public int insert(TmallGoods goods) {
		Gson gson = new Gson();
		String insertSql = "insert into tmall_goods("
				+ "brand,cat,catPath,commentsCount,id,name,price,salesCount,shopName,url,"
				+ "descUrls,colorUrls,imgUrls,params" + ") " + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] obj = new Object[] { goods.getBrand(), goods.getCat(), goods.getCatPath(), goods.getCommentsCount(),
				goods.getId(), goods.getName(), goods.getPrice(), goods.getSalesCount(), goods.getShopName(),
				goods.getUrl(), gson.toJson(goods.getDescUrls()), gson.toJson(goods.getColorUrls()),
				gson.toJson(goods.getImgUrls()), gson.toJson(goods.getParams()) };
		int[] type = new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
				Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
				Types.VARCHAR, Types.VARCHAR };
		int uss = jdbcTemplate.update(insertSql, obj, type);
		return uss;
	}

	/**
	 * 删除一个商品
	 * 
	 * @param goods
	 * @return
	 */
	public int delete(String id) {
		String deleteSql = "delete from tmall_goods where id = ?";
		Object[] obj = new Object[] { id };
		int[] type = new int[] { Types.VARCHAR };
		int res = jdbcTemplate.update(deleteSql, obj, type);
		return res;
	}

	/**
	 * 修改一条数据
	 */
	public int update(TmallGoods goods) {
		Gson gson = new Gson();
		String insertSql = "update tmall_goods "
				+ " set brand=?,cat=?,catPath=?,commentsCount=?, "
				+ " name=?,price=?,salesCount=?,shopName=?,url=?, "
				+ " descUrls=?,colorUrls=?,imgUrls=?,params=? " + "where id=?";
		Object[] obj = new Object[] { goods.getBrand(), goods.getCat(), goods.getCatPath(), goods.getCommentsCount(),
				goods.getName(), goods.getPrice(), goods.getSalesCount(), goods.getShopName(), goods.getUrl(),
				gson.toJson(goods.getDescUrls()), gson.toJson(goods.getColorUrls()),
				gson.toJson(goods.getImgUrls()), gson.toJson(goods.getParams()), 
				goods.getId()};
		int[] type = new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
				Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
				Types.VARCHAR, Types.VARCHAR };
		int uss = jdbcTemplate.update(insertSql, obj, type);
		return uss;
	}
	
	/**
	 * 修改一条数据
	 */
	public int updateGoodsWithoutCat(TmallGoods goods) {
		Gson gson = new Gson();
		String insertSql = "update tmall_goods "
				+ " set brand=?,commentsCount=?, "
				+ " name=?,price=?,salesCount=?,shopName=?,url=?, "
				+ " descUrls=?,colorUrls=?,imgUrls=?,params=? " + "where id=?";
		Object[] obj = new Object[] { goods.getBrand(), goods.getCommentsCount(),
				goods.getName(), goods.getPrice(), goods.getSalesCount(), goods.getShopName(), goods.getUrl(),
				gson.toJson(goods.getDescUrls()), gson.toJson(goods.getColorUrls()),
				gson.toJson(goods.getImgUrls()), gson.toJson(goods.getParams()), 
				goods.getId()};
		int[] type = new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
				Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
				Types.VARCHAR, Types.VARCHAR };
		int uss = jdbcTemplate.update(insertSql, obj, type);
		return uss;
	}
	
	public List<TmallGoods> getListByCatPath(String catPath) {
		String sql = "select * from tmall_goods where  catpath = ?";
		Object[] params = new Object[] { catPath };
		List<TmallGoods> list = (List<TmallGoods>) jdbcTemplate.query(sql, params, rowMapper);
		return list;
	}
	
	public List<TmallGoods> getListByCatPathContains(String catPath) {
		String sql = "select * from tmall_goods where  catpath like ?";
		Object[] params = new Object[] { "%"+catPath+"%" };
		List<TmallGoods> list = (List<TmallGoods>) jdbcTemplate.query(sql, params, rowMapper);
		return list;
	}

	public List<TmallGoods> getListByShopNameOrderByCat(String shopName) {
		String sql = "select * from tmall_goods where  shopname = ? order by cat";
		Object[] params = new Object[] { shopName };
		List<TmallGoods> list = (List<TmallGoods>) jdbcTemplate.query(sql, params, rowMapper);
		return list;
	}
}
